package com.spar.openprojects.server;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.jxls.transformer.XLSTransformer;

import org.apache.poi.ss.usermodel.Workbook;

import com.spar.openprojects.server.datasource.DatasourceMySqlOpenprojects;
import com.spar.openprojects.shared.OrderExcel;
import com.spar.openprojects.shared.OrderItem;
import com.spar.openprojects.shared.rowmapper.OrderExcelRowMapper;
import com.spar.openprojects.shared.rowmapper.OrderItemRowMapper;
import com.spar.openprojects.shared.utilities.LogManager;
import com.sql.database.Database;
import com.sql.database.IConnectionFactory;
import com.sql.database.IRowMapper;

public class OrderingExcelExportServlet extends HttpServlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = -3768817950809784492L;

	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		try {
			String orderId = request.getParameter("orderId");

			Database database = new Database(connectionFactory);
			IRowMapper rowMapper = new OrderExcelRowMapper();
			String sqlStatement = "select b.*, o.*, m.*, p.*, p.id 'sorder.sumOrder' from business_partner b, openprojects.order o, member m, project p where b.id = o.business_partner_id and p.member_id = m.id and o.project_id = p.id and o.id = "
					+ orderId;
			List<OrderExcel> ordersExcel = new ArrayList<OrderExcel>();
			try {
				ordersExcel = database.ExecuteQuery(sqlStatement, rowMapper);
			} catch (Exception e) {
				logManager.Log("Error", "load order", e.getMessage());
			}
			OrderExcel orderExcel = ordersExcel.get(0);

			// offer, order, bills
			rowMapper = new OrderItemRowMapper();
			sqlStatement = "select i.* from orderitem i where order_id = "
					+ orderId;
			List<OrderItem> orderItems = new ArrayList<OrderItem>();
			try {
				orderItems = database.ExecuteQuery(sqlStatement, rowMapper);
			} catch (Exception e) {
				logManager.Log("Error", "load orderitem", e.getMessage());
			}

			List<OrderItem> bills = new ArrayList<OrderItem>();
			for (OrderItem i : orderItems) {
				switch (i.getType()) {
				case 1:
					orderExcel.setOffer(i);
					break;
				case 2:
					orderExcel.setOrder(i);
					break;
				case 3:
					bills.add(i);
					break;
				}
			}
			orderExcel.setBills(bills);

			orderExcel.setSignature1("Hannes Leobacher");
			orderExcel.setSignature2("Matthias Bergetschwandtner");
			if (orderExcel.getOrder().getAmount() > 10000) {
				orderExcel.setSignature1("Hannes Leobacher");
				orderExcel.setSignature2("Johann Neumayr");
			}
			if (orderExcel.getOrder().getAmount() > 50000) {
				orderExcel.setSignature1("Hannes Leobacher");
				orderExcel.setSignature2("Andreas Kranabitl");
			}

			Format dateFormat = new SimpleDateFormat("yyyy_MM_dd");
			String orderDate = "";
			if (orderExcel.getOrder().getDate() != null) {
				orderDate = dateFormat.format(orderExcel.getOrder().getDate());
			}
			String filename = "Beauftragung_"
					+ orderExcel.getProject().getNumber() + "_"
					+ orderExcel.getProject().getDescription() + "_"
					+ orderDate + ".xls";
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ filename + "\"");

			String path = "./webapps/openprojects/";
			String pathDebug = "./";
			InputStream inputStream = new BufferedInputStream(
					new FileInputStream(path + "sampleorderform.xls"));
			Map beans = new HashMap();
			beans.put("order", orderExcel);
			XLSTransformer transformer = new XLSTransformer();
			Workbook wb = transformer.transformXLS(inputStream, beans);
			ServletOutputStream op = response.getOutputStream();
			wb.write(op);
			op.flush();
			op.close();

			logManager.Log("Info", "export excel", filename
					+ " successfully exported");
		} catch (Exception ex) {
			logManager.Log("Error", "export excel", ex.getMessage());
		}
	}
}
